package com.nervenets.shopping.hibernate.dao;

import com.nervenets.general.hibernate.dao.BaseDao;
import com.nervenets.shopping.hibernate.dao.vo.DayCount;
import com.nervenets.shopping.hibernate.domain.order.GroupBuyOrderUser;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.math.BigDecimal;
import java.util.List;

public interface GroupBuyOrderUserDao extends BaseDao<GroupBuyOrderUser> {
    @Query(value = "select count(gbou.id) from group_buy_order_user as gbou " +
            "left join user as u ON u.id = gbou.create_by " +
            "where gbou.ct between :startTime and :endTime " +
            "and u.del = 0 and gbou.del = 0 and status > 1 " +
            "and JSON_CONTAINS(u.parent_ids, concat('', :userId)) ", nativeQuery = true)
    long countUserState(@Param("userId") long userId, @Param("startTime") long startTime, @Param("endTime") long endTime);

    @Query(value = "select " +
            "FROM_UNIXTIME(o.ct/1000,'%Y-%m-%d') as days, count(o.id) as total " +
            "from group_buy_order_user as o " +
            "left join user as u on u.id = o.create_by " +
            "where o.ct between :startTime and :endTime " +
            "and u.del = 0 and o.del = 0 and status > 1 " +
            "and JSON_CONTAINS(u.parent_ids, concat('', :userId)) group by days", nativeQuery = true)
    List<DayCount> groupByDay(@Param("userId") long userId, @Param("startTime") long startTime, @Param("endTime") long endTime);

    @Query("select new java.math.BigDecimal(COALESCE(SUM(reward), 0)) from GroupBuyOrderUser where deleted = 0 and rewardTime > 0 ")
    BigDecimal sumOfReward();

    @Query("select new java.math.BigDecimal(COALESCE(SUM(reward), 0)) from GroupBuyOrderUser where deleted = 0 and rewardTime between :startTime and :endTime ")
    BigDecimal sumOfReward(@Param("startTime") long startTime, @Param("endTime") long endTime);

    @Query("select new java.math.BigDecimal(COALESCE(SUM(money-subsidy), 0)) from GroupBuyOrderUser where deleted = 0 and payTime > 0 ")
    BigDecimal sumOfOrderMoney();

    @Query("select new java.math.BigDecimal(COALESCE(SUM(money-subsidy), 0)) from GroupBuyOrderUser where deleted = 0 and payTime between :startTime and :endTime ")
    BigDecimal sumOfOrderMoney(@Param("startTime") long startTime, @Param("endTime") long endTime);

    @Query("select new java.math.BigDecimal(COALESCE(SUM(money-subsidy), 0)) from GroupBuyOrderUser where deleted = 0 and refundTime > 0 ")
    BigDecimal sumOfRefundMoney();

    @Query("select new java.math.BigDecimal(COALESCE(SUM(money-subsidy), 0)) from GroupBuyOrderUser where deleted = 0 and refundTime between :startTime and :endTime ")
    BigDecimal sumOfRefundMoney(@Param("startTime") long startTime, @Param("endTime") long endTime);

    @Query(value = "select * from group_buy_order_user where order_id is null and grouped_time = 0 and pay_time > 0 and pay_time < :timeout ", nativeQuery = true)
    List<GroupBuyOrderUser> findUngroupedOrder(@Param("timeout") long timeout);
}
